import matplotlib.pyplot as plt
import openpyxl


def best_fit(x, y):
    xbar = sum(x) / len(x)
    ybar = sum(y) / len(y)
    n = len(x)  # or len(y)

    numer = sum([xi * yi for xi, yi in zip(x, y)]) - n * xbar * ybar
    denum = sum([xi ** 2 for xi in x]) - n * xbar ** 2

    b = numer / denum
    a = ybar - b * xbar

    print('best fit line:\ny = {:.2f} + {:.2f}x'.format(a, b))
    print(b)

    return a, b


def Plot_Best_fit(i: int):
    x = [1, 2, 3, 4, 5, 6]
    y = []
    for j in range(2, 8):
        if ws.cell(i, j).value == "" or ws.cell(i, j).value == None:
            y.append(0)
        else:
            y.append(ws.cell(i, j).value)
    a, b = best_fit(x, y)

    ws.cell(i, 15).value = b
    plt.scatter(x, y)
    yfit = [a + b * xi for xi in x]
    plt.plot(x, yfit)
    plt.xticks(x, ["23/10", "23/11", "23/12", "24/01", "24/02", "24/03"])
    plt.ylabel("月使用量")
    plt.xlabel("月份")
    plt.title(ws.cell(i, 1).value)
    plt.rcParams['font.sans-serif'] = ['SimHei']
    plt.rcParams["axes.unicode_minus"] = False
    plt.savefig("//192.168.70.101/19计划管理部/01.计划/5，物控管理/Data/2024.04.22安全库存分析/pic/" + ws.cell(i,
                                                                                                              1).value + ".png",
                dpi=600)
    plt.close()
    ws.cell(i,
            1).hyperlink = "//192.168.70.101/19计划管理部/01.计划/5，物控管理/Data/2024.04.22安全库存分析/pic/" + ws.cell(
        i,
        1).value + ".png"

    # 库存数量分析



wb = openpyxl.load_workbook(
    r"\\192.168.70.101\19计划管理部\01.计划\5，物控管理\Data\2024.04.22安全库存分析\线缆安全库存分析.xlsx")
ws = wb.worksheets[0]

for m in range(2, ws.max_row + 1):
    Plot_Best_fit(m)
wb.save("1.xlsx")
